/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package net.easoftware.code.util;

import java.io.BufferedInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.NumberFormat;
import java.text.ParseException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;
import net.easoftware.code.beans.Columna;
import net.easoftware.code.error.EaError;
import net.sf.jxls.reader.ReaderBuilder;
import net.sf.jxls.reader.XLSReadStatus;
import net.sf.jxls.reader.XLSReader;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.xml.sax.SAXException;

/**
 *
 * @author waumovil-javaengineer
 */
public class ExcelProccesor {

    /**
     *
     * @param micreditGrids
     * @param colsModel
     * @return
     */
    public static OutputStream writeXlsFile(Map<Integer, List<List<Columna>>> micreditGrids, List<Columna> colsModel) {
        ByteArrayOutputStream content = new ByteArrayOutputStream(1024);
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet();
            Set<Entry<Integer, List<List<Columna>>>> es = micreditGrids.entrySet();
            int rowsCur = 0;
            NumberFormat nf = NumberFormat.getInstance();
            for (Map.Entry<Integer, List<List<Columna>>> e : es) {
                System.out.println("grid " + e.getKey());
                int colIndex = 0;
                HSSFRow row = sheet.createRow(rowsCur);
                for (Iterator<Columna> it = colsModel.iterator(); it.hasNext();) {
                    Columna c = it.next();
                    row.createCell(colIndex).setCellValue(c.getHeader()); //add title
                    colIndex++;
                }
                rowsCur++;
                for (List<Columna> lsRows : e.getValue()) {
                    HSSFRow rowGrid = sheet.createRow(rowsCur);
                    colIndex = 0;
                    for (Columna c : lsRows) {
                        try {
                            rowGrid.createCell(colIndex).setCellValue(nf.parse(c.getValor()).intValue());
                        }
                        catch (ParseException ex) {
                            rowGrid.createCell(colIndex).setCellValue(c.getValor());
                        }

                        colIndex++;
                    }
                    rowsCur++;
                }
            }
            wb.write(content);
            content.flush();
        }
        catch (Exception ex) {
            Logger.getLogger(ExcelProccesor.class.getName()).log(Level.SEVERE, null, ex);
        }
        return content;
    }

    /**
     *
     * @param xmlConfig
     * @param dataXLS
     * @param beans
     * @throws EaError
     */
    public static void readXlsFile(String xmlConfig, InputStream dataXLS, Map beans) throws EaError {
        try {
            InputStream inputXML = new BufferedInputStream(ExcelProccesor.class.getClass().getResourceAsStream(xmlConfig));
            XLSReader mainReader = ReaderBuilder.buildFromXML(inputXML);
            InputStream inputXLS = new BufferedInputStream(dataXLS);
            XLSReadStatus readStatus = mainReader.read(inputXLS, beans);
            if (!readStatus.isStatusOK()) {
                throw new EaError("Error al procesar archivo: " + dataXLS);
            }
        }
        catch (InvalidFormatException ex) {
            throw new EaError("Error formato invalido de archivo: " + dataXLS);
        }
        catch (IOException ex) {
            throw new EaError("Error al leer archivo: " + dataXLS);
        }
        catch (SAXException ex) {
            throw new EaError("Error en el archivo de configuraciones: " + dataXLS);
        }
    }
}
